﻿namespace Chain.IDAL
{
    using Chain.DBUtility;
    using Chain.Model;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Runtime.InteropServices;
    using System.Text;

    public class PointLog
    {
        public int Add(Chain.Model.PointLog model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("insert into PointLog(");
            builder.Append("PointMemID,PointNumber,PointChangeType,PointRemark,PointShopID,PointCreateTime,PointUserID,PointOrderCode,PointGiveMemID)");
            builder.Append(" values (");
            builder.Append("@PointMemID,@PointNumber,@PointChangeType,@PointRemark,@PointShopID,@PointCreateTime,@PointUserID,@PointOrderCode,@PointGiveMemID)");
            builder.Append(";select @@IDENTITY");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@PointMemID", SqlDbType.Int, 4), new SqlParameter("@PointNumber", SqlDbType.Int, 4), new SqlParameter("@PointChangeType", SqlDbType.TinyInt, 1), new SqlParameter("@PointRemark", SqlDbType.NVarChar, 0x3e8), new SqlParameter("@PointShopID", SqlDbType.Int, 4), new SqlParameter("@PointCreateTime", SqlDbType.DateTime), new SqlParameter("@PointUserID", SqlDbType.Int, 4), new SqlParameter("@PointOrderCode", SqlDbType.NVarChar, 40), new SqlParameter("@PointGiveMemID", SqlDbType.Int) };
            cmdParms[0].Value = model.PointMemID;
            cmdParms[1].Value = model.PointNumber;
            cmdParms[2].Value = model.PointChangeType;
            cmdParms[3].Value = model.PointRemark;
            cmdParms[4].Value = model.PointShopID;
            cmdParms[5].Value = model.PointCreateTime;
            cmdParms[6].Value = model.PointUserID;
            cmdParms[7].Value = model.PointOrderCode;
            cmdParms[8].Value = model.PointGiveMemID;
            object single = DbHelperSQL.GetSingle(builder.ToString(), cmdParms);
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

        public DataTable AgainPrint(int pointID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select ");
            builder.Append("MemCard,MemName,PointChangeType,PointNumber,MemPoint,UserName,PointCreateTime,PointRemark ");
            builder.Append("from ");
            builder.Append("Mem,PointLog,SysUser ");
            builder.Append("where Mem.MemID=PointLog.PointMemID ");
            builder.Append("and SysUser.UserID=PointLog.PointUserID ");
            builder.Append("and PointID=@PointID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@PointID", SqlDbType.Int, 4) };
            cmdParms[0].Value = pointID;
            return DbHelperSQL.Query(builder.ToString(), cmdParms).Tables[0];
        }

        public bool Delete(int PointID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from PointLog ");
            builder.Append(" where PointID=@PointID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@PointID", SqlDbType.Int, 4) };
            cmdParms[0].Value = PointID;
            return (DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }

        public bool DeleteList(string PointIDlist)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from PointLog ");
            builder.Append(" where PointID in (" + PointIDlist + ")  ");
            return (DbHelperSQL.ExecuteSql(builder.ToString()) > 0);
        }

        public int DeleteLog(string strOrderAccount)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from PointLog where PointOrderCode=@PointOrderCode");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("PointOrderCode", SqlDbType.VarChar, 50) };
            cmdParms[0].Value = strOrderAccount;
            return DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms);
        }

        public bool Exists(int PointID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select count(1) from PointLog");
            builder.Append(" where PointID=@PointID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@PointID", SqlDbType.Int, 4) };
            cmdParms[0].Value = PointID;
            return DbHelperSQL.Exists(builder.ToString(), cmdParms);
        }

        public bool Exists(string account)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select count(1) from PointLog");
            builder.Append(" where PointOrderCode=@PointOrderCode");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@PointOrderCode", SqlDbType.VarChar, 50) };
            cmdParms[0].Value = account;
            return DbHelperSQL.Exists(builder.ToString(), cmdParms);
        }

        public DataSet GetList(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select PointID,PointMemID,PointNumber,PointChangeType,PointRemark,PointShopID,PointCreateTime,PointUserID,PointOrderCode,PointGiveMemID ");
            builder.Append(" FROM PointLog ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(builder.ToString());
        }

        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select ");
            if (Top > 0)
            {
                builder.Append(" top " + Top.ToString());
            }
            builder.Append(" PointID,PointMemID,PointNumber,PointChangeType,PointRemark,PointShopID,PointCreateTime,PointUserID,PointOrderCode,PointGiveMemID ");
            builder.Append(" FROM PointLog ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            builder.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(builder.ToString());
        }

        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT * FROM ( ");
            builder.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                builder.Append("order by T." + orderby);
            }
            else
            {
                builder.Append("order by T.PointID desc");
            }
            builder.Append(")AS Row, T.*  from PointLog T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                builder.Append(" WHERE " + strWhere);
            }
            builder.Append(" ) TT");
            builder.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(builder.ToString());
        }

        public DataSet GetListSP(int PageSize, int PageIndex, out int resCount, params string[] strWhere)
        {
            string tableName = "PointLog,Mem,MemLevel,SysShop,SysUser";
            string[] column = new string[] { "PointLog.*,Mem.*,MemLevel.LevelName,SysShop.ShopName,SysUser.UserName" };
            string[] condition = strWhere;
            int recordCount = 1;
            DataSet set = DbHelperSQL.GetTable(tableName, column, condition, "PointID", false, PageSize, PageIndex, recordCount);
            resCount = int.Parse(set.Tables[1].Rows[0][0].ToString());
            return set;
        }

        public int GetMaxId()
        {
            return DbHelperSQL.GetMaxID("PointID", "PointLog");
        }

        public Chain.Model.PointLog GetModel(int PointID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select  top 1 PointID,PointMemID,PointNumber,PointChangeType,PointRemark,PointShopID,PointCreateTime,PointUserID,PointOrderCode,PointGiveMemID from PointLog ");
            builder.Append(" where PointID=@PointID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@PointID", SqlDbType.Int, 4) };
            cmdParms[0].Value = PointID;
            Chain.Model.PointLog log = new Chain.Model.PointLog();
            DataSet set = DbHelperSQL.Query(builder.ToString(), cmdParms);
            if (set.Tables[0].Rows.Count <= 0)
            {
                return null;
            }
            if ((set.Tables[0].Rows[0]["PointID"] != null) && (set.Tables[0].Rows[0]["PointID"].ToString() != ""))
            {
                log.PointID = int.Parse(set.Tables[0].Rows[0]["PointID"].ToString());
            }
            if ((set.Tables[0].Rows[0]["PointMemID"] != null) && (set.Tables[0].Rows[0]["PointMemID"].ToString() != ""))
            {
                log.PointMemID = int.Parse(set.Tables[0].Rows[0]["PointMemID"].ToString());
            }
            if ((set.Tables[0].Rows[0]["PointNumber"] != null) && (set.Tables[0].Rows[0]["PointNumber"].ToString() != ""))
            {
                log.PointNumber = int.Parse(set.Tables[0].Rows[0]["PointNumber"].ToString());
            }
            if ((set.Tables[0].Rows[0]["PointChangeType"] != null) && (set.Tables[0].Rows[0]["PointChangeType"].ToString() != ""))
            {
                log.PointChangeType = int.Parse(set.Tables[0].Rows[0]["PointChangeType"].ToString());
            }
            if ((set.Tables[0].Rows[0]["PointRemark"] != null) && (set.Tables[0].Rows[0]["PointRemark"].ToString() != ""))
            {
                log.PointRemark = set.Tables[0].Rows[0]["PointRemark"].ToString();
            }
            if ((set.Tables[0].Rows[0]["PointShopID"] != null) && (set.Tables[0].Rows[0]["PointShopID"].ToString() != ""))
            {
                log.PointShopID = int.Parse(set.Tables[0].Rows[0]["PointShopID"].ToString());
            }
            if ((set.Tables[0].Rows[0]["PointCreateTime"] != null) && (set.Tables[0].Rows[0]["PointCreateTime"].ToString() != ""))
            {
                log.PointCreateTime = DateTime.Parse(set.Tables[0].Rows[0]["PointCreateTime"].ToString());
            }
            if ((set.Tables[0].Rows[0]["PointUserID"] != null) && (set.Tables[0].Rows[0]["PointUserID"].ToString() != ""))
            {
                log.PointUserID = int.Parse(set.Tables[0].Rows[0]["PointUserID"].ToString());
            }
            if ((set.Tables[0].Rows[0]["PointOrderCode"] != null) && (set.Tables[0].Rows[0]["PointOrderCode"].ToString() != ""))
            {
                log.PointOrderCode = set.Tables[0].Rows[0]["PointOrderCode"].ToString();
            }
            if ((set.Tables[0].Rows[0]["PointGiveMemID"] != null) && (set.Tables[0].Rows[0]["PointGiveMemID"].ToString() != ""))
            {
                log.PointGiveMemID = Convert.ToInt32(set.Tables[0].Rows[0]["PointGiveMemID"]);
            }
            return log;
        }

        public DataSet GetPointByTime(DateTime starttime, DateTime endtime, string strwhere)
        {
            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@StartTime", SqlDbType.DateTime), new SqlParameter("@EndTime", SqlDbType.DateTime), new SqlParameter("@strWhere", SqlDbType.NVarChar, 0x7d0) };
            parameters[0].Value = starttime;
            parameters[1].Value = endtime;
            parameters[2].Value = strwhere;
            return DbHelperSQL.RunProcedure("MonthPointLog", parameters, "#PointLogData");
        }

        public int GetPointChange(string strSql)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(" select isnull(sum(PointNumber),0) from PointLog,Mem ");
            if (strSql.Trim() != "")
            {
                builder.Append(" where " + strSql);
            }
            object single = DbHelperSQL.GetSingle(builder.ToString());
            if (single != null)
            {
                return Convert.ToInt32(single);
            }
            return 0;
        }

        public int GetPointNumber(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select SUM(PointNumber) FROM PointLog,Mem,MemLevel,SysShop,SysUser ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            object single = DbHelperSQL.GetSingle(builder.ToString());
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

        public int GetRecordCount(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select count(1) FROM PointLog ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            object single = DbHelperSQL.GetSingle(builder.ToString());
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

        public bool IsSignedToday(int memId, int pointLogType)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT COUNT(0) FROM PointLog");
            builder.Append(" WHERE PointMemID =@PointMemID");
            builder.Append(" AND PointChangeType = @PointChangeType");
            builder.Append(" AND PointCreateTime BETWEEN CONVERT(varchar(10),GETDATE(),120) AND CONVERT(varchar(10),DATEADD(dd,1,GETDATE()),120)");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@PointMemID", SqlDbType.Int, 4), new SqlParameter("@PointChangeType", SqlDbType.Int, 4) };
            cmdParms[0].Value = memId;
            cmdParms[1].Value = pointLogType;
            return DbHelperSQL.Exists(builder.ToString(), cmdParms);
        }

        public int MemSign(Chain.Model.Mem member)
        {
            if (member == null)
            {
                return -1;
            }
            return 1;
        }

        public bool Update(Chain.Model.PointLog model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update PointLog set ");
            builder.Append("PointMemID=@PointMemID,");
            builder.Append("PointNumber=@PointNumber,");
            builder.Append("PointChangeType=@PointChangeType,");
            builder.Append("PointRemark=@PointRemark,");
            builder.Append("PointShopID=@PointShopID,");
            builder.Append("PointCreateTime=@PointCreateTime,");
            builder.Append("PointUserID=@PointUserID,");
            builder.Append("PointOrderCode=@PointOrderCode,");
            builder.Append("PointGiveMemID=@PointGiveMemID");
            builder.Append(" where PointID=@PointID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@PointMemID", SqlDbType.Int, 4), new SqlParameter("@PointNumber", SqlDbType.Int, 4), new SqlParameter("@PointChangeType", SqlDbType.TinyInt, 1), new SqlParameter("@PointRemark", SqlDbType.NVarChar, 0x3e8), new SqlParameter("@PointShopID", SqlDbType.Int, 4), new SqlParameter("@PointCreateTime", SqlDbType.DateTime), new SqlParameter("@PointUserID", SqlDbType.Int, 4), new SqlParameter("@PointID", SqlDbType.Int, 4), new SqlParameter("@PointOrderCode", SqlDbType.NVarChar, 40), new SqlParameter("@PointGiveMemID", SqlDbType.Int) };
            cmdParms[0].Value = model.PointMemID;
            cmdParms[1].Value = model.PointNumber;
            cmdParms[2].Value = model.PointChangeType;
            cmdParms[3].Value = model.PointRemark;
            cmdParms[4].Value = model.PointShopID;
            cmdParms[5].Value = model.PointCreateTime;
            cmdParms[6].Value = model.PointUserID;
            cmdParms[7].Value = model.PointID;
            cmdParms[8].Value = model.PointOrderCode;
            cmdParms[9].Value = model.PointGiveMemID;
            return (DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }

        public int UpdateMemPoint(int memID, int intPointNumber)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update Mem set MemPoint=MemPoint-@MemPoint");
            builder.Append(" where MemID=@MemID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@MemPoint", SqlDbType.Int, 4), new SqlParameter("@MemID", SqlDbType.Int, 4) };
            cmdParms[0].Value = intPointNumber;
            cmdParms[1].Value = memID;
            return DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms);
        }

        public int UpdatePointLog(string strOrderCode, int intPointNumber, int intMemID, string strRemark)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(" update PointLog set PointNumber=@PointNumber，");
            builder.Append(" PointRemark=@PointRemark");
            builder.Append(" where PointOrderCode=@PointOrderCode and PointMemID=@PointMemID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@PointNumber", SqlDbType.Int, 4), new SqlParameter("@PointRemark", SqlDbType.VarChar, 0x3e8), new SqlParameter("@PointOrderCode", SqlDbType.VarChar, 50), new SqlParameter("@PointMemID", SqlDbType.Int, 4) };
            cmdParms[0].Value = intPointNumber;
            cmdParms[1].Value = strRemark;
            cmdParms[2].Value = strOrderCode;
            cmdParms[3].Value = intMemID;
            return DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms);
        }
    }
}

